﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Backend
{
    /* Customer Class
     * This class will handle all the customers and the 
     * methods to create, update and delete a customer. */
    class Customer
    {
		DBConnect dbconn = new DBConnect();

        public DataTable List(string telephone = "", string fullName = "", int brandID = 0)
        {
            DataTable customerTable;

            if (fullName != "")
            {
                customerTable = dbconn.BetterDataTable("SELECT cid, fullname, zipcode, telephone FROM customer WHERE fullname LIKE '%" + fullName + "%'", 4);
            }
            else if (telephone != "")
            {
                customerTable = dbconn.BetterDataTable("SELECT cid, fullname, zipcode, telephone FROM customer WHERE telephone LIKE '%" + telephone + "%'", 4);
            }
            else if (brandID != 0)
            {
                customerTable = dbconn.BetterDataTable("SELECT c.cid, c.fullname, c.zipcode, c.telephone FROM customer AS c, scooter AS s WHERE s.cid = c.cid AND s.bid = '" + brandID + "'", 4);
            }
            else
                customerTable = dbconn.BetterDataTable("SELECT cid, fullname, zipcode, telephone FROM customer", 4);

            
            return customerTable;
        }

        public string[] GetInfo(int cid)
        {
            string[] customerInfo = dbconn.ReturnArray("SELECT cid, fullname, address, zipcode, telephone, email FROM customer WHERE cid = '" + cid + "'", 6);

            Array.Resize<string>(ref customerInfo, 7);
            customerInfo[6] = dbconn.RunQueryReturn("SELECT city FROM city WHERE zipcode = '" + customerInfo[3] + "' LIMIT 1");
            
            Array.Resize<string>(ref customerInfo, 8);
            customerInfo[7] = dbconn.RunQueryReturn("SELECT e.fullname FROM employee AS e, `order` AS o WHERE e.eid = o.eid AND o.cid = '" + cid + "' ORDER BY o.date DESC LIMIT 1");
            
            return customerInfo;
        }

        public bool CheckZipcode(int zipcode)
        {
            int results;
            string query;

            query = dbconn.RunQueryReturn("SELECT COUNT(*) FROM city WHERE zipcode = '" + zipcode + "'");
            results = Convert.ToInt32(query);

            if (results > 0)
                return true;
            else
                return false;
        }

		public void Create(string fullName, string address, int zipcode, int telephone, string email, out bool result, out int clientID)
		{
            if (CheckZipcode(zipcode) != true)
            {
                result = false;
                clientID = 0;
            }
            else
            {
                string[] insertSet = new string[5];

                insertSet[0] = fullName; // Customer's fullname
                insertSet[1] = address; // Customer's address
                insertSet[2] = zipcode.ToString(); // Customer's zipcode
                insertSet[3] = telephone.ToString(); // Customer's telephone
                insertSet[4] = email; // Customer's e-mail address

                dbconn.RunQuery("INSERT INTO customer (fullname, address, zipcode, telephone, email) VALUES ('" + string.Join("', '", insertSet) + "')");
                clientID = Convert.ToInt32(dbconn.RunQueryReturn("SELECT MAX(cid) FROM customer"));
                result = true;

            }
		}

        public bool Update(int cid, string fullName, string address, int zipcode, int telephone, string email)
        {
            int results;
            string query;

            query = dbconn.RunQueryReturn("SELECT COUNT(*) FROM customer WHERE cid = '" + cid + "'");
            results = Convert.ToInt32(query);

            if (results < 0)
                return false;
            else
            {
                if (CheckZipcode(zipcode) != true)
                    return false;
                else
                {
                    string[] updateSet = new string[5];

                    updateSet[0] = "fullname = '" + fullName + "'";
                    updateSet[1] = "address = '" + address + "'";
                    updateSet[2] = "zipcode = '" + zipcode + "'";
                    updateSet[3] = "telephone = '" + telephone + "'";
                    updateSet[4] = "email = '" + email + "'";

                    dbconn.RunQuery("UPDATE customer SET " + string.Join(", ", updateSet) + " WHERE cid = '" + cid + "' LIMIT 1");
                    return true;
                }
            }
        }

        public bool Check(int cid)
        {
            int results;
            string query;

            query = dbconn.RunQueryReturn("SELECT COUNT(*) FROM customer WHERE cid = '" + cid + "'");
            results = Convert.ToInt32(query);

            if (results > 0)
                return true;
            else
                return false;
        }
    }
}
